MoveFirst, MoveLast, MoveNext, MovePrevious Methods Example

This example uses the MoveFirst, MoveLast, MoveNext, and MovePrevious methods to move the record pointer of a Recordset based on the supplied command. The MoveAny procedure is required for this procedure to run.

Sub MoveFirstX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim strMessage As String
    Dim intCommand As Integer

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = dbsNorthwind.OpenRecordset( _
        "SELECT FirstName, LastName FROM Employees " & _
        "ORDER BY LastName", dbOpenSnapshot)

    With rstEmployees
        ' Populate Recordset.
        .MoveLast
        .MoveFirst
        Do While True
            ' Show current record information and get user's
            ' method choice.
            strMessage = "Name: " & !FirstName & " " & _
                !LastName & vbCr & "Record " & _
                (.AbsolutePosition + 1) & " of " & _
                .RecordCount & vbCr & vbCr & _
                "[1 - MoveFirst, 2 - MoveLast, " & vbCr & _
                "3 - MoveNext, 4 - MovePrevious]"
            intCommand = Val(Left(InputBox(strMessage), 1))
            If intCommand < 1 Or intCommand > 4 Then Exit Do

            ' Call method based on user's input.
            MoveAny intCommand, rstEmployees
        Loop
        .Close
    End With

    dbsNorthwind.Close

End Sub

Sub MoveAny(intChoice As Integer, _
    rstTemp As Recordset)

    ' Use specified method, trapping for BOF and EOF.
    With rstTemp
        Select Case intChoice
            Case 1
                .MoveFirst
            Case 2
                .MoveLast
            Case 3
                .MoveNext
                If .EOF Then
                    MsgBox "Already at end of recordset!"
                    .MoveLast
                End If
            Case 4
                .MovePrevious
                If .BOF Then
                    MsgBox "Already at beginning of recordset!"
                    .MoveFirst
                End If
        End Select
    End With

End Sub